package team02.server.bean;

import java.util.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import team02.server.model.MySQLModel;
import team02.server.model.organization.OrgModel;

public class OrganizationBean extends MySQLModel implements OrgModel {

	public OrganizationBean() {
		super(null);
	}
	private static final String FIELD_NAME = " `alias`, `name`, `description`, `phone`, `address`,"
			+ " `status`, `registered_date`, `expired_date`, `admin`";
	private String alias;
	private String name;
	private String address;
	private long registeredDate;
	private long expiredDate;
	private String phone;
	private String description;
	private UserBean admin;
	private String adminname;
	private String username;
	private String password;
	private String email;
	private int status;
	private int NumberOfTest=0;
	private int price=0;

	public int getStatus() {
		return status;
	}

	public void setStatus(int status) {
		this.status = status;
	}

	public UserBean getAdmin() {
		return admin;
	}

	public void setAdmin(UserBean admin) {
		this.admin = admin;
	}

	public String getPhone() {
		return this.phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public void setAlias(String alias) {
		this.alias = alias;
	}

	public String getDescription() {
		return this.description;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	public String getEmail() {
		return this.email;
	}

	public void setEmail(String email) {
		this.email = email;
	}


	public long getRegisteredDate() {
		return registeredDate;
	}

	public void setRegisteredDate(long registeredDate) {
		this.registeredDate = registeredDate;
	}
	public void setRegisteredDate(String str){
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
		java.util.Date dt;
		try {
			dt = df.parse(str);
			this.registeredDate=dt.getTime();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public long getExpiredDate() {
		return expiredDate;
	}
public void setExpiredDate(long ex){
	this.expiredDate=ex;
}
	public void setExpiredDate(String expiredDate) {
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
		java.util.Date dt;
		try {
			dt = df.parse(expiredDate);
			this.registeredDate=dt.getTime();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public String getAlias() {
		return this.alias;
	}
//公開の団体一覧を収得する
	public List<OrganizationBean> listAllOrganization() throws SQLException {
		//		if(!"default".equals(alias)) return null;
//		 " `alias`, `name`, `description`, `phone`, `address`, `status`, `registered_date`, `expired_date`, `alias`, `admin`
		String query = "SELECT " + FIELD_NAME + " FROM " + TABLE_ORG + " WHERE alias NOT LIKE 'default' AND status='0'";
//		System.out.println("QUERY = " + query);
		initConnection();
		
		executeQuery(query);
		List<OrganizationBean> listOrg = new LinkedList<OrganizationBean>();
		
		while(resultSet.next()) {
			OrganizationBean org = new OrganizationBean();
			org.alias = resultSet.getString(1);
			org.name = resultSet.getString(2);
			org.description = resultSet.getString(3);
			org.phone = resultSet.getString(4);
			org.address = resultSet.getString(5);
			org.status = resultSet.getInt(6);
			org.registeredDate = resultSet.getLong(7);
			org.expiredDate = resultSet.getLong(8);

			UserBean orgAdmin = new UserBean();
			//orgAdmin.setUserId(resultSet.getInt(9));
			orgAdmin.setAlias(resultSet.getString(1));
			orgAdmin.loadOrgAdmin(resultSet.getString(1));
			org.admin = orgAdmin;
			listOrg.add(org);
		}
		freeResources();
		return listOrg;
	}
	public List<OrganizationBean> listTrashOrganization() throws SQLException {
		//		if(!"default".equals(alias)) return null;
//		 " `alias`, `name`, `description`, `phone`, `address`, `status`, `registered_date`, `expired_date`, `alias`, `admin`
		String query = "SELECT " + FIELD_NAME + " FROM " + TABLE_ORG + " WHERE alias NOT LIKE 'default' AND status='1'";
//		System.out.println("QUERY = " + query);
		initConnection();
		
		executeQuery(query);
		List<OrganizationBean> listOrg = new LinkedList<OrganizationBean>();
		
		while(resultSet.next()) {
			OrganizationBean org = new OrganizationBean();
			org.alias = resultSet.getString(1);
			org.name = resultSet.getString(2);
			org.description = resultSet.getString(3);
			org.phone = resultSet.getString(4);
			org.address = resultSet.getString(5);
			org.status = resultSet.getInt(6);
			org.registeredDate = resultSet.getLong(7);
			org.expiredDate = resultSet.getLong(8);

			UserBean orgAdmin = new UserBean();
			//orgAdmin.setUserId(resultSet.getInt(9));
			orgAdmin.setAlias(resultSet.getString(1));
			orgAdmin.loadOrgAdmin(resultSet.getString(1));
			org.admin = orgAdmin;
			listOrg.add(org);
		}
		freeResources();
		return listOrg;
	}

	//	public void setAlias(String alias) throws Exception {
	//		// if (StringUtils.equals(alias, DEFAULT)) return;
	//		if (StringUtils.equals(this.alias, alias)) return;
	//
	//		Connection connection = initConnection();
	//		Statement statement = null;
	//		ResultSet rs = null;
	//		try {
	//			statement = (Statement) connection.createStatement();
	//			rs = statement.executeQuery("SELECT name, address,id FROM " + TABLE_ORG + " WHERE alias LIKE '" + alias
	//					+ "'");
	//			if (rs.next()) {
	//				setName(rs.getString("name"));
	//				setAddress(rs.getString("address"));
	//				setOrgId(rs.getInt("id"));
	//				this.alias = alias;
	//			} else {
	//				throw new Exception("Khong thay doan the nao co alias la " + alias);
	//			}
	//		} finally {
	//			if (rs != null) rs.close();
	//			if (statement != null) statement.close();
	//			if (connection != null) connection.close();
	//		}
	//	}

	@Override
	public String getName() {
		return this.name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Override
	public String getAddress() {
		return this.address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	/**
	 * 全ての団体を収得する
	 * 
	 * @return
	 */

	/**
	 * 新規団体を追加
	 * 
	 * @param orgName
	 * @param orgUserName
	 * @param orgDescription
	 * @param orgPassword
	 * @param orgEmail
	 * @param orgMobile
	 * @param orgAddress
	 * @param orgAlias
	 * @param orgExpiredDate
	 * @return
	 */

	public int load() throws SQLException {
		if(alias!=null){
			initConnection();
			String query="SELECT * FROM jp_organization WHERE alias='"+alias+"'";
			executeQuery(query);
			if (this.resultSet.next()) {
				this.alias=this.resultSet.getString("alias");
				this.name=this.resultSet.getString("name");
				this.address=this.resultSet.getString("address");
				this.phone=this.resultSet.getString("phone");
				this.expiredDate=this.resultSet.getLong("expired_date");
				this.registeredDate=this.resultSet.getLong("registered_date");
				this.description=this.resultSet.getString("description");

			} else {
				freeResources();
				return 1;
			}
			query="SELECT * FROM jp_user WHERE alias='"+alias+"' AND access_level= '2'";
			executeQuery(query);
			if (this.resultSet.next()) {
				this.adminname=this.resultSet.getString("name");
				this.username=this.resultSet.getString("username");
				this.email=this.resultSet.getString("email");
				this.password=this.resultSet.getString("password");
				freeResources();
				
			} else {
				freeResources();
				return 1;
			}
			return 0;
		}
		else return 0;
	}

	public int save() throws SQLException {
			initConnection();
			String query;
			java.util.Date dt=new java.util.Date();
			long today=dt.getTime();
			//if(expiredDate<today)return 1;
			try{
				query="UPDATE jp_organization SET name='"+name+"',description='"+description+"', phone='"+phone+"', status='"+status+"', expired_date='"+expiredDate+"'WHERE alias='"+alias+"'";
				System.out.println(query);
				executeUpdate(query);
			}
			catch (Exception e) {
				return 1;
				// TODO: handle exception
			}
			query="UPDATE jp_user SET name='"+adminname+"', username='"+username+"', password='"+password+"', email='"+email+"' WHERE alias='"+alias+"' AND access_level=2";
			try{
				
				executeUpdate(query);
			}catch (Exception e) {
				return 1;
				// TODO: handle exception
			}
			return 0;
	}

	@Override
	public int insert() throws SQLException {
		java.util.Date dt=new java.util.Date();
		long registered=dt.getTime();
		try{
			initConnection();
			String query="INSERT INTO jp_organization (name, description, phone, address,alias, registered_date,expired_date) value('"+name+"','"+description+"','"+phone+"','"+address+"','"+alias+"','"+registered+"','"+expiredDate+"')";
			executeUpdate(query);
			
		}catch (Exception e) {
		return 1;
			// TODO: handle exception
		}
		try{
			initConnection();
			String query="INSERT INTO jp_user (name, username, password, email, alias, access_level, status) VALUE('"+adminname+"','"+username+"','"+password+"','"+email+"','"+alias+"','2','1')";
			executeUpdate(query);
			
		}catch (Exception e) {
		return 1;
			// TODO: handle exception
		}
		return 0;
	}

	@Override
	public int delete() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public void clear() {
		// TODO Auto-generated method stub

	}

	public String getAdminname() {
		return adminname;
	}

	public void setAdminname(String adminname) {
		this.adminname = adminname;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}
	public String getRegisteredDateString()
	{
		java.sql.Date dt=new java.sql.Date(registeredDate);
		return dt.toString();
	}
	public String getExpiredDateString()
	{
		java.sql.Date dt=new java.sql.Date(expiredDate);
		return dt.toString();
	}
	//団体を非公開にする
	public int blockOrgByAlias(String a){
		try{
			initConnection();
			String query="UPDATE jp_organization SET status='1' WHERE alias='"+a+"'";
			executeUpdate(query);
		}catch (Exception e) {
			System.out.println(e);
			return 1;
			// TODO: handle exception
		}
		return 0;
	}
	//団体を公開にする
	public int enableOrgByAlias(String a){
		try{
			initConnection();
			String query="UPDATE jp_organization SET status='0' WHERE alias='"+a+"'";
			executeUpdate(query);
		}catch (Exception e) {
			System.out.println(e);
			return 1;
			// TODO: handle exception
		}
		return 0;
	}
	//団体を削除する
	public int deleteOrgByAlias(String a){
		try{
			initConnection();
			String query="DELETE FROM jp_user WHERE alias='"+a+"'";
			executeUpdate(query);
			query="DELETE FROM jp_test WHERE alias='"+a+"'";
			executeUpdate(query);
			query="DELETE FROM  jp_organization WHERE alias='"+a+"'";
			executeUpdate(query);


		}catch (Exception e) {
			System.out.println(e);
			return 1;
			// TODO: handle exception
		}
		return 0;
	}
	//契約期間満了か契約期間内かチェックする
	public void refresh(){

		java.util.Date td=new java.util.Date();
		String a;
		long today=td.getTime();
		try{
			initConnection();
			String query="SELECT alias FROM jp_organization WHERE expired_date<'"+today+"'-63072000000";
			executeQuery(query);
			while(this.resultSet.next())
			{
				a=this.resultSet.getString("alias");
				this.deleteOrgByAlias(a);
			}
			query="SELECT alias FROM jp_organization WHERE expired_date<"+today+"";
			executeQuery(query);
			while(this.resultSet.next())
			{
				a=this.resultSet.getString("alias");
				this.blockOrgByAlias(a);
			}
			freeResources();
		}catch (Exception e) {
			// TODO: handle exception
		}
	}
	public boolean checkAliasRequest(String a) {
		try{
			initConnection();
			String query="SELECT * FROM jp_organization WHERE alias='"+a+"'";
			executeQuery(query);
			if(resultSet.next()) return true;
			else return false;
		}catch (Exception e) {
			return false;
			// TODO: handle exception
		}
		
	}
	//今月の課金をまとめる
	public List<OrganizationBean> listAllOrganizationMoney() throws SQLException {
		java.util.Date dt=new java.util.Date();
		int month=dt.getMonth();
		int year=dt.getYear();
		Calendar c=Calendar.getInstance();
		c.setTime(dt);
		int lastDayOfMonth = c.getActualMaximum( Calendar.DAY_OF_MONTH);
		Date enddate=new Date(year, month, lastDayOfMonth);
		int firstDayOfMonth = c.getActualMinimum( Calendar.DAY_OF_MONTH);
		Date startdate=new Date(year, month, firstDayOfMonth);
		
		initConnection();
		int unit_price=0;
		//lay don gia cho moi bai test.
		String query="SELECT unit_price,from_date FROM jp_price ORDER BY from_date DESC";
		executeQuery(query);
		ResultSet price_array=resultSet;
		//lay danh sach cac dantai
		List<OrganizationBean> listOrg = new OrganizationBean().listAllOrganization();
		List<OrganizationBean> listOrgMoney = new LinkedList<OrganizationBean>();
		//Lay danh sach cac bai test cua tung dantai
		for (Iterator<OrganizationBean> i = listOrg.iterator();i.hasNext();) {
			OrganizationBean org=i.next();
			query="select count(*) AS id,start_date_time from jp_examinee,jp_test where jp_examinee.test_id=jp_test.id and jp_test.alias='"+org.alias+"' AND jp_test.start_date_time>="+startdate.getTime()+" AND jp_test.start_date_time<="+enddate.getTime()+" group by jp_examinee.test_id  ";
			executeQuery(query);
			while(resultSet.next())
			{
				org.NumberOfTest++;
				price_array.beforeFirst();
				while(price_array.next())
				{
					if(resultSet.getLong("start_date_time")>=price_array.getLong("from_date"))
					{
						unit_price=price_array.getInt("unit_price");
						break;
					}
				}
				org.price+=resultSet.getInt(1)*unit_price;
			}
			if(org.NumberOfTest>0)listOrgMoney.add(org);
		}
		price_array.close();
		freeResources();
		return listOrgMoney;
		
	}
//任意の期間の課金情報をまとめる
	public List<OrganizationBean> listAllOrganizationMoney(String date) throws SQLException, ParseException{
		java.util.Date dt=new SimpleDateFormat("MMMM yyyy").parse(date);
		int month=dt.getMonth();
		int year=dt.getYear();
		Calendar c=Calendar.getInstance();
		c.setTime(dt);
		int lastDayOfMonth = c.getActualMaximum( Calendar.DAY_OF_MONTH);
		Date enddate=new Date(year, month, lastDayOfMonth);
		int firstDayOfMonth = c.getActualMinimum( Calendar.DAY_OF_MONTH);
		Date startdate=new Date(year, month, firstDayOfMonth);
		
		initConnection();
		int unit_price=0;
		//lay don gia cho moi bai test.
		String query="SELECT unit_price,from_date FROM jp_price ORDER BY from_date DESC";
		executeQuery(query);
		ResultSet price_array=resultSet;
		//lay danh sach cac dantai
		List<OrganizationBean> listOrg = new OrganizationBean().listAllOrganization();
		List<OrganizationBean> listOrgMoney = new LinkedList<OrganizationBean>();
		//Lay danh sach cac bai test cua tung dantai
		for (Iterator<OrganizationBean> i = listOrg.iterator();i.hasNext();) {
			OrganizationBean org=i.next();
			query="select count(*) AS id,start_date_time from jp_examinee,jp_test where jp_examinee.test_id=jp_test.id and jp_test.alias='"+org.alias+"' AND jp_test.start_date_time>="+startdate.getTime()+" AND jp_test.start_date_time<="+enddate.getTime()+" group by jp_examinee.test_id  ";
			executeQuery(query);
			while(resultSet.next())
			{
				org.NumberOfTest++;
				price_array.beforeFirst();
				while(price_array.next())
				{
					if(resultSet.getLong("start_date_time")>=price_array.getLong("from_date"))
					{
						unit_price=price_array.getInt("unit_price");
						break;
					}
				}
				org.price+=resultSet.getInt(1)*unit_price;
			}
			if(org.NumberOfTest>0)listOrgMoney.add(org);
		}
		price_array.close();
		freeResources();
		return listOrgMoney;
		
	}
	public int getNumberOfTest() {
		return NumberOfTest;
	}

	public void setNumberOfTest(int numberOfTest) {
		NumberOfTest = numberOfTest;
	}

	public int getPrice() {
		return price;
	}

	public void setPrice(int price) {
		this.price = price;
	}
}
